<?php
require_once "../global.php";
require_once "../modul/print.php";
check_pass();
require_once "laporan-spt.php";
baca_permisi(4);
global $permisi;
if($permisi["access"]=="tolak"){
	header('Location:../warning.php');
}
        global $inp_par,$inp,$permisi,$cdivisi_kode;

        $namaFile = "reportSPTMasukan.xls";
        header("Content-type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=$namaFile");
        header("Cache-control: public");
        $inp_par[hari]=$inp_par[hari]==""?1:$inp_par[hari];
	$inp_par[bulan]=$inp_par[bulan]==""?date('n'):$inp_par[bulan];
	$inp_par[tahun]=$inp_par[tahun]==""?date('Y'):$inp_par[tahun];
	$inp_par[hari_]=$inp_par[hari_]==""?date('j'):$inp_par[hari_];
	$inp_par[bulan_]=$inp_par[bulan_]==""?date('n'):$inp_par[bulan_];
	$inp_par[tahun_]=$inp_par[tahun_]==""?date('Y'):$inp_par[tahun_];
// Validasi Akses Divisi
    if ($cdivisi_kode=='KP'){
          $cond_divisi_kode = " a.divisi_kode!=''";
    }else{
          $divisi_wilayah = explode(".",$cdivisi_kode);
          $divisi_len = strlen($divisi_wilayah[0]);
        if ($cdivisi_kode==$divisi_wilayah[0]){
              $cond_divisi_kode = " substr(a.divisi_kode,1,$divisi_len)='".$divisi_wilayah[0]."'";
        }else{
              $cond_divisi_kode = " a.divisi_kode='".$cdivisi_kode."'";
        }
    }

        if ($inp_par[buku]=="Belum"){
            $SQLCombo .= " AND is_buku='N'";
        }else if($inp_par[buku]=="Sudah"){
            $SQLCombo .= " AND is_buku='Y'";
        }else{
            $SQLCombo .= "";
        }

        if ($inp_par[bayar]=="Belum"){
            $SQLCombo .= " AND is_bayar='N'";
        }else if($inp_par[bayar]=="Sudah"){
            $SQLCombo .= " AND is_bayar='Y'";
        }else{
            $SQLCombo .= "";
        }

        if ($inp_par[lapor]=="Belum"){
            $SQLCombo .= " AND is_lapor='N'";
        }else if($inp_par[lapor]=="Sudah"){
            $SQLCombo .= " AND is_lapor='Y'";
        }else{
            $SQLCombo .= "";
        }

        $periode="and tgl_faktur>='".$inp_par[tahun]."-".$inp_par[bulan]."-".$inp_par[hari]."' and tgl_faktur<='".$inp_par[tahun_]."-".$inp_par[bulan_]."-".$inp_par[hari_]."'";
        $SQLcari = "select a.no_faktur_pajak,c.nama_leveransir,c.npwp_leveransir,a.bukti_pembayaran,d.nama_proyek,b.divisi_name from tbl_spt a
                    JOIN tbl_divisi b ON (a.divisi_kode = b.divisi_kode)
                    JOIN m_leveransir c ON (a.id_leveransir = c.id_leveransir)
                    JOIN m_proyek d ON (a.id_proyek = d.id_proyek) where $cond_divisi_kode";
        $kata=strtolower(trim($inp_par[cari]));
	if($kata<>""){
                $sqlq = getRSq($SQLcari);
                $jmlField = pg_num_fields($sqlq);
                for ($x=0;$x<$jmlField;$x++){
                    $namaField = pg_field_name($sqlq, $x);
                    $cond .= "lower(CAST(".$namaField." as TEXT)) LIKE lower('%$kata%') OR ";
                }
                $sql_tambahan = substr($cond,0,strlen($cond)-3);
		$sql_tambahan = " and ($sql_tambahan)";
	}
        $period = $inp_par[hari]."/".$inp_par[bulan]."/".$inp_par[tahun]." - ".$inp_par[hari_]."/".$inp_par[bulan_]."/".$inp_par[tahun_];

 //$rwilayah = getRS("SELECT divisi_name FROM tbl_divisi WHERE divisi_kode='".$cdivisi_kode."'");
 $rwilayah = getNameFromID("tbl_divisi","divisi_name","AND divisi_kode='".$cdivisi_kode."'");
                $txtout .="<table width='900px' border='1' align='center' cellpadding='0' cellspacing='1'>";
                $txtout .="<tr>";
                $txtout .="<td colspan=11 class=huruf>PT. NINDYA KARYA";
                $txtout .="</td>";
                $txtout .="</tr>";
                $txtout .="<tr>";
                $txtout .="<td colspan=11 class=huruf>".$rwilayah."";
                $txtout .="</td>";
                $txtout .="</tr>";
                $txtout .="<tr>";
                $txtout .="<td colspan=11 class=huruf></font>";
                $txtout .="</td>";
                $txtout .="</tr>";
                $txtout .="<tr><td colspan=11><div align=center class=title>";
                $txtout .="<b>DAFTAR FAKTUR PAJAK</b>";
                $txtout .="</div></td></tr>";
                $txtout .="<tr><td colspan=11 class=huruf><div align=center class=huruf>";
                $txtout .="PERIODE : $period";
                $txtout .="</div></td></tr>";
                $txtout .="<tr>";
                $txtout .="<td colspan=11 height='2'>&nbsp;</font>";
                $txtout .="</td>";
                $txtout .="</tr>";

        //$sql10 = "SELECT DISTINCT divisi_kode FROM tbl_spt ".$cond_divisi_kode.$SQL1.$SQL2."";
        $sqlDivisi = "SELECT DISTINCT a.divisi_kode from tbl_spt a
              JOIN tbl_divisi b ON (a.divisi_kode = b.divisi_kode)
              JOIN m_leveransir c ON (a.id_leveransir = c.id_leveransir)
                JOIN m_proyek d ON (a.id_proyek = d.id_proyek) where $cond_divisi_kode $periode $SQLCombo $sql_tambahan";
        $result1 = getRSq($sqlDivisi);
        $jmltotdpp = 0;
        $jmltotppn = 0;
        while($rs1=pg_fetch_array($result1)){
        $nama_divisi = getNameFromID("tbl_divisi","divisi_name","AND divisi_kode='".trim($rs1['divisi_kode'])."'");
        $txtout .="<tr bgcolor=#FFFFFF height=25>
                <td colspan='11' class=\"huruftot\">&nbsp;".$nama_divisi."</td>
                </tr>";
        $txtout .="<tr>";
                $txtout .="<td height='25' width='2%' class=\"b_kiri b_atas b_bawah huruf5\">NO</td>";
//                $txtout .="<td width='8%' class=\"b_kiri b_atas b_bawah huruf5\"><strong>WILAYAH</strong></td>";
                $txtout .="<td width='8%' class=\"b_kiri b_atas b_bawah huruf5\"><strong>NO F. PAJAK</strong></td>";
                $txtout .="<td width='10%' class=\"b_kiri b_atas b_bawah huruf5\"><strong>TGL F. PAJAK</strong></td>";
                $txtout .="<td width='14%' class=\"b_kiri b_atas b_bawah huruf5\"><strong>NAMA REKANAN</strong></td>";
                $txtout .="<td width='12%' class=\"b_kiri b_atas b_bawah huruf5\"><strong>NPWP REKANAN</strong></td>";
                $txtout .="<td width='20%' class=\"b_kiri b_atas b_bawah huruf5\"><strong>ALAMAT REKANAN</strong></td>";
                $txtout .="<td width='10%' class=\"b_kiri b_atas b_bawah huruf5\"><strong>BUKTI PEMBUKUAN</strong></td>";
                $txtout .="<td width='10%' class=\"b_kiri b_kanan b_atas b_bawah huruf5\"><strong>BUKTI PEMBAYARAN</strong></td>";
                $txtout .="<td width='10%' class=\"b_kiri b_kanan b_atas b_bawah huruf5\"><strong>LAPOR SPT MASA</strong></td>";
                $txtout .="<td width='10%' class=\"b_kiri b_kanan b_atas b_bawah huruf5\"><strong>DPP</strong></td>";
                $txtout .="<td width='10%' class=\"b_kiri b_kanan b_atas b_bawah huruf5\"><strong>PPN(10%)</strong></td>";
                $txtout .="</tr>";
        $sqlProyek = "SELECT DISTINCT a.id_proyek from tbl_spt a
              JOIN tbl_divisi b ON (a.divisi_kode = b.divisi_kode)
              JOIN m_leveransir c ON (a.id_leveransir = c.id_leveransir)
                JOIN m_proyek d ON (a.id_proyek = d.id_proyek) where $cond_divisi_kode and a.divisi_kode='$rs1[divisi_kode]' $periode $SQLCombo $sql_tambahan";
        $resultP = getRSq($sqlProyek);
        $totdpp = 0;
        $totppn = 0;
        while($rsP=pg_fetch_array($resultP)){
        $sql = "select a.*,b.divisi_name,c.nama_leveransir,c.npwp_leveransir,c.alamat_leveransir,d.nama_proyek from tbl_spt a
                 JOIN tbl_divisi b ON (a.divisi_kode = b.divisi_kode)
                 JOIN m_leveransir c ON (a.id_leveransir = c.id_leveransir)
                JOIN m_proyek d ON (a.id_proyek = d.id_proyek) where $cond_divisi_kode and a.divisi_kode='$rs1[divisi_kode]' $periode $SQLCombo $sql_tambahan and a.id_proyek='$rsP[id_proyek]' order by tgl_faktur asc ,no_faktur_pajak asc";
        $result = getRSq($sql);
        $no = 0;
        $warna = '#FFFFFF';

        $nama_proyek = getNameFromID("m_proyek","nama_proyek","AND id_proyek='".trim($rsP['id_proyek'])."'");
        $txtout .="<tr bgcolor=#FFFFFF height=25>
                <td colspan='11' class=\"b_kiri b_kanan b_bawah huruf4\">&nbsp;PROYEK :&nbsp;".$nama_proyek."</td>
                </tr>";
        $dpp=0;
        $ppn=0;
	while($r=pg_fetch_array($result)){
            $no++;
            if ($warna == '#FFFFFF'){
                $warna = '#E8EFFF';
            }else{
                $warna = '#FFFFFF';
            }
            if ($r['is_buku']=='N' && $r['is_bayar']=='N'){
                $warna = 'red';
            }
            $rsL1 = getRS("SELECT * FROM m_leveransir WHERE id_leveransir!='' AND id_leveransir='".trim($r['id_leveransir'])."'");
            if ($rsL1!=""){
                $NamaLeveransir1 = $rsL1['nama_leveransir'];
                $NPWPLeveransir1 = $rsL1['npwp_leveransir'];
                $AlamatLeveransir1 = $rsL1['alamat_leveransir'];
            }
            $lapor = explode("-",$r[tanggal_lapor]);
            $tanggal_lapor = $lapor[1]."-".$lapor[0];

        $txtout .="<tr bgcolor=#FFFFFF height=25>
            <td width='2%' class=\"b_kiri huruf4\">&nbsp;&nbsp;".$no."</td>
            <td width='8%' class=\"b_kiri huruf4\">&nbsp;".$r['no_faktur_pajak']."</td>
            <td width='10%' class=\"b_kiri huruf4\">&nbsp;".$r['tgl_faktur']."</td>
            <td width='14%' class=\"b_kiri huruf4\">&nbsp;".$NamaLeveransir1."</td>
            <td width='12%' class=\"b_kiri huruf4\">&nbsp;".$NPWPLeveransir1."</td>
            <td width='20%' class=\"b_kiri huruf4\">&nbsp;".$AlamatLeveransir1."</td>
            <td width='10%' class=\"b_kiri huruf4\">&nbsp;".$r[bukti_pembukuan]."</td>
            <td width='10%' class=\"b_kiri huruf4\">&nbsp;".$r[bukti_pembayaran]."</td>
            <td width='10%' class=\"b_kiri huruf4\">&nbsp;".$tanggal_lapor."</td>
            <td width='10%' align='right' class=\"b_kiri huruf4\">".number_format($r['dpp'])."&nbsp;</td>
            <td width='10%' align='right' class=\"b_kiri b_kanan huruf4\">".number_format($r['ppn'])."&nbsp;</td>
            </tr>
            ";
        $dpp +=$r['dpp'];
        $ppn +=$r['ppn'];
        }
        $txtout .="<tr height=25>
                    <td align='left' colspan='9' class=\"b_kiri b_atas b_bawah huruftot\">&nbsp;TOTAL PROYEK $nama_proyek</td>
                    <td align='right' class=\"b_kiri b_atas b_bawah huruftot\">".number_format($dpp)."&nbsp;</td>
                    <td align='right' class=\"b_kiri b_kanan b_atas b_bawah huruftot\">".number_format($ppn)."&nbsp;</td>
                    </tr>
                    ";
        $totdpp +=$dpp;
        $totppn +=$ppn;
    }
    if ($totdpp!=0 || $totppn!=0){
        $txtout .="<tr height=25>
                    <td align='left' colspan='9' class=\"b_kiri b_bawah huruftot\">&nbsp;TOTAL $nama_divisi</td>
                    <td align='right' class=\"b_kiri b_bawah huruftot\">".number_format($totdpp)."&nbsp;</td>
                    <td align='right' class=\"b_kiri b_kanan b_bawah huruftot\">".number_format($totppn)."&nbsp;</td>
                    </tr>
                    ";
        }
        $jmltotdpp +=$totdpp;
        $jmltotppn +=$totppn;
        }
        if ($jmltotdpp!=0 || $jmltotppn!=0){
        $txtout .="<tr height=25>
                    <td align='left' colspan='9' class=\"b_kiri b_bawah huruftot\">&nbsp;TOTAL</td>
                    <td align='right' class=\"b_kiri b_bawah huruftot\">".number_format($jmltotdpp)."&nbsp;</td>
                    <td align='right' class=\"b_kiri b_kanan b_bawah huruftot\">".number_format($jmltotppn)."&nbsp;</td>
                    </tr>
                    ";
        }

//                
		$txtout2 .="<br>";
        $txtout3 .="</table>";
        $txtoutTot = $txtout.$txtout2.$txtout3;
        echo $txtoutTot;
?>
